package cn.com.dao.impl;

import cn.com.dao.StudentDao;
import cn.com.domain.Student;
import cn.com.domain.User;
import cn.com.utils.jdbcutils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class StudentDaoImpl implements StudentDao {
    @Override
    public List<Student> findAll() {
        List<Student> list=new ArrayList<>();
        try {
            Connection conn= jdbcutils.getConnection();
            String sql="select * from student";
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()){
                //封装Student对象并添加到list集合
                int sid=rs.getInt("sid");
                String stuname=rs.getString("stuname");
                String sex=rs.getString("sex");
                String academy=rs.getString("academy");
                Student stu=new Student(sid,stuname,sex,academy);
                list.add(stu);
            }
            jdbcutils.close(rs,stmt,conn);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public Student findStudentByname(String stuname) {
        Student stu=null;
        try {
            Connection conn= jdbcutils.getConnection();
            String sql="select * from user where stuname = ? ";
            PreparedStatement pst=conn.prepareStatement(sql);
            pst.setString(1,stuname);
            ResultSet rs = pst.executeQuery();
            //如果查到了就封装为对象
            if (rs.next()){
                int sid=rs.getInt("sid");
                String sex=rs.getString("sex");
                String academy=rs.getString("academy");
                stu=new Student(sid,stuname,sex,academy);
            }
            //释放资源
            jdbcutils.close(rs,pst,conn);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return stu;
    }

    @Override
    public Student findStudentBysid(int sid) {
        Student stu=null;
        try {
            Connection conn= jdbcutils.getConnection();
            String sql="select * from user where sid = ? ";
            PreparedStatement pst=conn.prepareStatement(sql);
            pst.setInt(1,sid);
            ResultSet rs = pst.executeQuery();
            //如果查到了就封装为对象
            if (rs.next()){
                String stuname=rs.getString("stuname");
                String sex=rs.getString("sex");
                String academy=rs.getString("academy");
                stu=new Student(sid,stuname,sex,academy);
            }
            //释放资源
            jdbcutils.close(rs,pst,conn);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return stu;
    }

    @Override
    public void addStudent(Student stu) {
        try {
            Connection conn=jdbcutils.getConnection();
            String sql="insert into student values(?,?,?,?)";
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setInt(1,stu.getSid());
            pst.setString(2,stu.getStuname());
            pst.setString(3,stu.getSex());
            pst.setString(4,stu.getAcademy());
            pst.executeUpdate();
            jdbcutils.close(pst,conn);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void deleteStudent(Student stu) {
        try {
            Connection conn=jdbcutils.getConnection();
            String sql="delete from student where stuname = ? and sid = ?";
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setString(1,stu.getStuname());
            pst.setInt(2,stu.getSid());
            pst.executeUpdate();
            jdbcutils.close(pst,conn);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    //修改学生信息
    public void alterStudent(Student beforestu,Student nowstu) {
        //删除修改前的学生，添加修改后的
        deleteStudent(beforestu);
        addStudent(nowstu);
    }
}
